﻿using System;
using System.Data;
using System.Data.SqlClient;
using CRMCommon.DataSets;
using E4RPTools;

namespace CRMCommon
{
    public class Controller
    {
        #region Members

        private string _connectionString;
        
        #endregion

        #region Properties

        #endregion

        #region Public Methods

        public void SetupConnection(string dataSource, string databaseName, string userName, string password)
        {
            _connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + databaseName + ";User ID=" + userName +
            ";Password=" + password + ";MultipleActiveResultSets=True;";
        }

        public ClienteDataSet GetClients(string filter)
        {
            var dataSet = new ClienteDataSet();
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(dataSet.Cliente, _connectionString, 
                    @"  select	Id,
                                Nome,
                                Contribuinte as NumeroContribuinte,
                                Fotografia,
                                Morada,
                                CodigoPostal,
                                Localidade,
                                Cidade,
                                Distrito,
                                Telefone,
                                Email,
                                CessouActividade,
                                IdTipoCliente,
                                IdGestorCliente,
                                Observacoes
		                from	Cliente
                        where   Nome like @Filter or Contribuinte like @Filter", new[] { new SqlParameter("Filter", String.Format("%{0}%", filter)) });
            return dataSet;
        }

        public ClienteDataSet GetClientById(long clientId)
        {
            var dataSet = new ClienteDataSet();
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(dataSet.Cliente, _connectionString,
                    @"  select	Id,
                                Nome,
                                Contribuinte as NumeroContribuinte,
                                Fotografia,
                                Morada,
                                CodigoPostal,
                                Localidade,
                                Cidade,
                                Distrito,
                                Telefone,
                                Email,
                                CessouActividade,
                                IdTipoCliente,
                                IdGestorCliente,
                                Observacoes
		                from	Cliente
                        where   Id = @ClientId", new[] { new SqlParameter("ClientId", clientId) });
            GetContacts(clientId, dataSet.Contacto);
            return dataSet;
        }

        public AgendaDataSet GetSchedule()
        {
            var dataSet = new AgendaDataSet();
            const string query = @"  select	Id,
                            Nome,
                            DataInicio,
                            DataFim,
                            IdFuncionario,
                            IdCliente,
                            IdContacto
		            from	Ocorrencia";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(dataSet.Agenda, _connectionString, query);
            return dataSet;
        }

        public FrotaDataSet GetFleet(DateTime start, DateTime end)
        {
            var dataSet = new FrotaDataSet();
            const string query = @"  select	Id,
                            IdFuncionario,
                            DataInicio,
                            DataFim,
                            Localizacao
		            from	Frota
                    where   DataInicio >= @Start and
                            DataInicio <= @End";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(dataSet.Frota, _connectionString, query, new[] { new SqlParameter("Start", start), new SqlParameter("End", end) });
            return dataSet;
        }

        public HistoricoDataSet GetHistory(int occorenceId)
        {
            var dataSet = new HistoricoDataSet();
            const string query = @"  select	o1.Id,
                            o1.Nome,
                            DataInicio,
                            DataFim,
                            IdFuncionario,
                            f.Nome as Funcionario,
                            IdCliente,
                            c.Nome as Cliente,
                            IdContacto
		            from	Ocorrencia o1, Funcionario f, Cliente c
                    where   exists 
                            (
                                select  1
                                from    Ocorrencia o2
                                where   ((o1.IdCliente is not null and o2.IdCliente = o1.IdCliente) or
                                        (o1.IdContacto is not null and o2.IdContacto = o1.IdContacto)) and
                                        o2.Id = @Id
                            ) and
                            c.Id = o1.IdCliente and
                            f.Id = o1.IdFuncionario";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(dataSet.Historico, _connectionString, query, new[] { new SqlParameter("Id", occorenceId) });
            return dataSet;
        }

        public FuncionarioDataSet GetEmployees()
        {
            var dataSet = new FuncionarioDataSet();
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(dataSet.Funcionario, _connectionString, 
                    @"  select	Id,
                                Nome
		                from	Funcionario");
            return dataSet;
        }

        public void Insert(DateTime startDate, DateTime endDate, int employeeId, string name, int? clientId, int? contactId)
        {
            var connection = new SqlConnection(_connectionString);
            connection.Open();
            var transaction = connection.BeginTransaction();
            const string query = @"  insert 
                    into    Ocorrencia(DataInicio, DataFim, IdFuncionario, Nome, IdCliente, IdContacto)
                    values  (@StartDate, @EndDate, @EmployeeId, @Name, @ClientId, @ContactId)";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.ExecuteNonQuery(transaction, query,
                                           new[]
                                               {
                                                   new SqlParameter("StartDate", startDate),
                                                   new SqlParameter("EndDate", endDate),
                                                   new SqlParameter("EmployeeId", employeeId),
                                                   new SqlParameter("Name", name), 
                                                   new SqlParameter("ClientId", clientId ?? Convert.DBNull),
                                                   new SqlParameter("ContactId", contactId ?? Convert.DBNull)
                                               });
            }
            transaction.Commit();
            connection.Close();
        }

        public void InsertClient(ClienteDataSet.ClienteDataTable cliente)
        {
            var connection = new SqlConnection(_connectionString);
            connection.Open();
            var transaction = connection.BeginTransaction();
            const string query = @"  insert 
                    into    Cliente(Nome, Contribuinte, Fotografia, Morada, CodigoPostal, Localidade, Cidade, Distrito, Telefone, Email,
                            CessouActividade, IdTipoCliente, Observacoes, IdGestorCliente)
                    values  (@Name, @FiscalNumber, @Foto, @Address, @PostalCode, @Town, @City, @District, @Telephone, @Email, @CeasedActivity,
                            @ClientTypeId, @Remarks, @ClientManagerId)";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.ExecuteNonQuery(transaction, query,
                                           new[]
                                               {
                                                   new SqlParameter("Name", cliente[0].Nome),
                                                   new SqlParameter("FiscalNumber", cliente[0].NumeroContribuinte), 
                                                   new SqlParameter("Foto", SqlDbType.Image, cliente[0].IsFotografiaNull() ? 0 : cliente[0].Fotografia.Length, ParameterDirection.Input,
                                                       true, 0, 0, "", DataRowVersion.Current, cliente[0].IsFotografiaNull() ? Convert.DBNull : cliente[0].Fotografia),
                                                   new SqlParameter("Address", cliente[0].IsMoradaNull() ? Convert.DBNull : cliente[0].Morada),
                                                   new SqlParameter("PostalCode", cliente[0].IsCodigoPostalNull() ? Convert.DBNull : cliente[0].CodigoPostal),
                                                   new SqlParameter("Town", cliente[0].IsLocalidadeNull() ? Convert.DBNull : cliente[0].Localidade),
                                                   new SqlParameter("City", cliente[0].IsCidadeNull() ? Convert.DBNull : cliente[0].Cidade),
                                                   new SqlParameter("District", cliente[0].IsDistritoNull() ? Convert.DBNull : cliente[0].Distrito),
                                                   new SqlParameter("Telephone", cliente[0].IsTelefoneNull() ? Convert.DBNull : cliente[0].Telefone),
                                                   new SqlParameter("Email", cliente[0].IsEmailNull() ? Convert.DBNull : cliente[0].Email),
                                                   new SqlParameter("CeasedActivity", cliente[0].IsCessouActividadeNull() ? Convert.DBNull : Convert.ToInt16(cliente[0].CessouActividade)),
                                                   new SqlParameter("ClientTypeId", cliente[0].IsIdTipoClienteNull() ? Convert.DBNull : cliente[0].IdTipoCliente),
                                                   new SqlParameter("Remarks", cliente[0].IsObservacoesNull() ? Convert.DBNull : cliente[0].Observacoes),
                                                   new SqlParameter("ClientManagerId", cliente[0].IsIdGestorClienteNull() ? Convert.DBNull : cliente[0].IdGestorCliente) 
                                               });
            }
            transaction.Commit();
            connection.Close();
        }

        public void UpdateClient(ClienteDataSet.ClienteDataTable cliente)
        {
            var connection = new SqlConnection(_connectionString);
            connection.Open();
            var transaction = connection.BeginTransaction();
            const string query = @"  update  Cliente 
                    set Nome = @Name, Contribuinte = @FiscalNumber, Fotografia = @Foto,
                        Morada = @Address, CodigoPostal = @PostalCode, Localidade = @Town,
                        Cidade = @City, Distrito = @District, Telefone = @Telephone,
                        Email = @Email, CessouActividade = @CeasedActivity, IdTipoCliente = @ClientTypeId,
                        Observacoes = @Remarks, IdGestorCliente = @ClientManagerId
                    where   Id = @ClientId";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.ExecuteNonQuery(transaction, query,
                                           new[]
                                               {
                                                   new SqlParameter("Name", cliente[0].Nome),
                                                   new SqlParameter("FiscalNumber", cliente[0].NumeroContribuinte), 
                                                   new SqlParameter("Foto", SqlDbType.Image, cliente[0].IsFotografiaNull() ? 0 : cliente[0].Fotografia.Length, ParameterDirection.Input,
                                                       true, 0, 0, "", DataRowVersion.Current, cliente[0].IsFotografiaNull() ? Convert.DBNull : cliente[0].Fotografia),
                                                   new SqlParameter("Address", cliente[0].IsMoradaNull() ? Convert.DBNull : cliente[0].Morada),
                                                   new SqlParameter("PostalCode", cliente[0].IsCodigoPostalNull() ? Convert.DBNull : cliente[0].CodigoPostal),
                                                   new SqlParameter("Town", cliente[0].IsLocalidadeNull() ? Convert.DBNull : cliente[0].Localidade),
                                                   new SqlParameter("City", cliente[0].IsCidadeNull() ? Convert.DBNull : cliente[0].Cidade),
                                                   new SqlParameter("District", cliente[0].IsDistritoNull() ? Convert.DBNull : cliente[0].Distrito),
                                                   new SqlParameter("Telephone", cliente[0].IsTelefoneNull() ? Convert.DBNull : cliente[0].Telefone),
                                                   new SqlParameter("Email", cliente[0].IsEmailNull() ? Convert.DBNull : cliente[0].Email),
                                                   new SqlParameter("CeasedActivity", cliente[0].IsCessouActividadeNull() ? Convert.DBNull : Convert.ToInt16(cliente[0].CessouActividade)),
                                                   new SqlParameter("ClientTypeId", cliente[0].IsIdTipoClienteNull() ? Convert.DBNull : cliente[0].IdTipoCliente),
                                                   new SqlParameter("Remarks", cliente[0].IsObservacoesNull() ? Convert.DBNull : cliente[0].Observacoes),
                                                   new SqlParameter("ClientManagerId", cliente[0].IsIdGestorClienteNull() ? Convert.DBNull : cliente[0].IdGestorCliente),
                                                   new SqlParameter("ClientId", cliente[0].Id)
                                               });
            }
            transaction.Commit();
            connection.Close();
        }

        #endregion

        #region Private Methods

        private void GetContacts(long clientId, DataTable table)
        {
            const string query = @"  select	Id,
                            Nome,
                            IdCliente
		            from	Contacto
                    where   IdCliente = @ClientId";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(table, _connectionString, query, new[] { new SqlParameter("ClientId", clientId) });
        }
        
        #endregion

        
    }
}
